﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using JKF.DataModule.IDAL;
using JKF.Entities.Model;
using JKF.Substructure;

namespace JKF.DataModule.SqlServerDAL
{
    /// <summary>
    /// 数据访问类dalSALES_NUTRITION。
    /// </summary>
    public partial class dalSALES_NUTRITION : IdalSALES_NUTRITION
    {
        private static string FieldToShow = "SNV.NUTRITION_KEY,SNV.PURCHASE_DATE,SNV.UNIQUE_CODE,SNV.CARD_NO,SNV.CUSTOMER_NAME,SNV.PRODUCT_MODEL,SNV.MOBILE,SNV.PHONE,SNV.PRODUCT_NAME,SNV.NUMS,SNV.PRICE,SNV.[MONEY],SNV.ADD_POINT,SNV.STATUS_NAME,SNV.OPERATOR,SNV.MEMO,SNV.DATE_CREATE";
        private static string FromTable = "VL_SALES_NUTRITION_VIEW SNV";
        private static string FieldKey = "SNV.NUTRITION_KEY";
        private static string FieldOrder = "SNV.DATE_UPDATE desc";

        /// <summary>
        /// 返回营养系列分页数据
        /// </summary>
        /// <param name="buyStart"></param>
        /// <param name="buyEnd"></param>
        /// <param name="status"></param>
        /// <param name="ownShops"></param>
        /// <param name="filter"></param>
        /// <param name="filterOp"></param>
        /// <param name="filterKey"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetSalesNutritionPaging(string buyStart, string buyEnd, string status, string ownShops,
            string filter, string filterOp, string filterKey, string currentUser, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sqlBuilder = new StringBuilder("");//used to join
            sqlBuilder.AppendFormat(" SNV.SHOP_CODE in (select shop_code from sys_user_shop where user_key='{0}')", currentUser);
            DateTime dtTmp;
            if (DateTime.TryParse(buyStart, out dtTmp)) sqlBuilder.AppendFormat(" AND SNV.PURCHASE_DATE>='{0}'", dtTmp.ToShortDateString());
            if (DateTime.TryParse(buyEnd, out dtTmp)) sqlBuilder.AppendFormat(" AND SNV.PURCHASE_DATE<='{0}'", dtTmp.ToShortDateString() + " 23:59:59");
            if (!string.IsNullOrEmpty(status)) sqlBuilder.AppendFormat(" AND SNV.STATUS='{0}'", status);
            if (!string.IsNullOrEmpty(ownShops)) sqlBuilder.AppendFormat(" AND SNV.SHOP_CODE in('{0}')", string.Join("','", ownShops.Split(',')));

            if (!string.IsNullOrEmpty(filterKey) && !string.IsNullOrEmpty(filterOp) && !string.IsNullOrEmpty(filter))
            {
                switch (filter.ToLower())
                {
                    case "card_no":
                        sqlBuilder.Append(BuildCondition("SNV.card_no", filterKey, filterOp));
                        break;
                    case "customer_name":
                        sqlBuilder.Append(BuildCondition("SNV.CUSTOMER_NAME", filterKey, filterOp));
                        break;
                    case "mobile":
                        sqlBuilder.Append(BuildCondition("SNV.[mobile]", filterKey, filterOp));
                        break;
                    case "unique_code":
                        sqlBuilder.Append(BuildCondition("SNV.[UNIQUE_CODE]", filterKey, filterOp));
                        break;
                    case "product_name":
                        sqlBuilder.Append(BuildCondition("SNV.product_name", filterKey, filterOp));
                        break;
                    case "model":
                        sqlBuilder.Append(BuildCondition("SNV.PRODUCT_MODEL", filterKey, filterOp));
                        break;
                    case "memo":
                        sqlBuilder.Append(BuildCondition("SNV.[memo]", filterKey, filterOp));
                        break;
                    case "operator":
                        sqlBuilder.AppendFormat((filterOp == "="
                                                     ? " And SNUT.USER_CREATE in (select USER_KEY from sys_user where real_name='{0}')"
                                                     : " And SNUT.USER_CREATE in (select USER_KEY from sys_user where real_name like '%{0}%')"), filterKey);
                        break;
                }
            }
            var lockDay = dalSYS_PARAM_VALUE.GetSalesDataLockDays();
            return lockDay > 0 ? DataPage.GetPageInfo(FromTable, FieldKey, pageCurrent, pageSize,
                FieldToShow + string.Format(" , datediff(dd,dateadd(dd,{0},DATE_CREATE),getdate()) AS isLock ", lockDay), FieldOrder, sqlBuilder.ToString(), ref pageCount, ref rowCount).Tables[0] :
                DataPage.GetPageInfo(FromTable, FieldKey, pageCurrent, pageSize, FieldToShow + " ,-1 AS isLock ", FieldOrder, sqlBuilder.ToString(), ref pageCount, ref rowCount).Tables[0];

        }

        /// <summary>
        /// 返回营养系列分页数据
        /// </summary>
        /// <param name="customerKey"></param>
        /// <param name="currentUser"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetSalesNutritionOfCustomerPaging(string customerKey, string currentUser, int pageCurrent, int pageSize,
                                                       ref int pageCount, ref int rowCount)
        {
            return DataPage.GetPageInfo(FromTable, FieldKey, pageCurrent, pageSize, FieldToShow, FieldOrder,
                string.Format(" SNV.SHOP_CODE IN (SELECT SHOP_CODE FROM SYS_USER_SHOP WHERE USER_KEY='{0}') AND SNV.CUSTOMER_KEY='{1}'", currentUser, customerKey),
                ref pageCount, ref rowCount).Tables[0];
        }

        /// <summary>
        /// 获得顾客的营养品销售记录 Customer Info View
        /// </summary>
        /// <param name="customerKey"></param>
        /// <param name="currentUser"></param>
        /// <returns></returns>
        public static DataTable GetNutritionByCustomer(string customerKey, string currentUser)
        {
            var sqlBuilder = new StringBuilder("select ");//used to join
            sqlBuilder.AppendFormat(" {0} from {1} where ", FieldToShow, FromTable);
            sqlBuilder.AppendFormat(" SNV.SHOP_CODE in (select shop_code from sys_user_shop where user_key='{0}')", currentUser);
            sqlBuilder.AppendFormat(" and snv.customer_key='{0}'", customerKey);
            sqlBuilder.AppendFormat(" order by {0}", FieldOrder);
            return DB.HR.ExecuteDataSet(CommandType.Text, sqlBuilder.ToString()).Tables[0];
        }

        /// <summary>
        /// 添加营养系列数据
        /// </summary>
        /// <param name="nutritionInfo"></param>
        /// <param name="pointLogInfo"></param>
        /// <param name="outInfo"></param>
        /// <returns></returns>
        public static bool SalesNutritionInsert(SALES_NUTRITIONinfo nutritionInfo, POINT_LOGinfo pointLogInfo, STOCK_OUTinfo outInfo)
        {
            var rslt = 1;
            var db = DB.HR;
            var dbCommand = db.GetStoredProcCommand("proc_sales_nutrition_insert");
            db.AddInParameter(dbCommand, "@NUTRITION_KEY", DbType.Guid, nutritionInfo.NUTRITION_KEY);
            db.AddInParameter(dbCommand, "@PRODUCT_KEY", DbType.Guid, nutritionInfo.PRODUCT_KEY);
            db.AddInParameter(dbCommand, "@UNIQUE_CODE", DbType.String, nutritionInfo.UNIQUE_CODE);
            db.AddInParameter(dbCommand, "@CUSTOMER_KEY", DbType.Guid, nutritionInfo.CUSTOMER_KEY);
            db.AddInParameter(dbCommand, "@NUMS", DbType.Int32, nutritionInfo.NUMS);
            db.AddInParameter(dbCommand, "@PRICE", DbType.Decimal, nutritionInfo.PRICE);
            db.AddInParameter(dbCommand, "@MONEY", DbType.Decimal, nutritionInfo.MONEY.HasValue ? nutritionInfo.MONEY.Value : 0);
            if (nutritionInfo.PURCHASE_DATE != null) db.AddInParameter(dbCommand, "@PURCHASE_DATE", DbType.DateTime, nutritionInfo.PURCHASE_DATE.Value);
            db.AddInParameter(dbCommand, "@ADD_POINT", DbType.Int32, pointLogInfo.POINT_VALUE.HasValue ? pointLogInfo.POINT_VALUE : 0);
            db.AddInParameter(dbCommand, "@POINT_KEY", DbType.Guid, pointLogInfo.POINT_KEY);
            db.AddInParameter(dbCommand, "@MEMO", DbType.String, nutritionInfo.MEMO);
            db.AddInParameter(dbCommand, "@OUT_KEY", DbType.Guid, outInfo.OUT_KEY);
            db.AddInParameter(dbCommand, "@SHOP_CODE", DbType.String, nutritionInfo.SHOP_CODE);
            db.AddInParameter(dbCommand, "@USER_CREATE", DbType.Guid, nutritionInfo.USER_CREATE);
            db.AddInParameter(dbCommand, "@POINT_WAY", DbType.String, pointLogInfo.WAY);
            db.AddInParameter(dbCommand, "@POINT_CODE", DbType.String, pointLogInfo.CODE);
            db.AddInParameter(dbCommand, "@OUT_NO", DbType.String, outInfo.OUT_NO);
            db.AddOutParameter(dbCommand, "@rslt", DbType.Int32, rslt);
            var ds = db.ExecuteNonQuery(dbCommand);
            rslt = int.Parse(dbCommand.Parameters["@rslt"].Value.ToString());
            return rslt == 1;
        }

        /// <summary>
        /// 编辑营养系列数据
        /// </summary>
        /// <param name="nutritionInfo"></param>
        /// <returns></returns>
        public static bool SalesNutritionEdit(SALES_NUTRITIONinfo nutritionInfo)
        {
            var rslt = 1;
            var db = DB.HR;
            var dbCommand = db.GetStoredProcCommand("proc_sales_nutrition_update");
            db.AddInParameter(dbCommand, "@NUTRITION_KEY", DbType.Guid, nutritionInfo.NUTRITION_KEY);
            db.AddInParameter(dbCommand, "@PRODUCT_KEY", DbType.Guid, nutritionInfo.PRODUCT_KEY);
            if (nutritionInfo.PURCHASE_DATE != null) db.AddInParameter(dbCommand, "@PURCHASE_DATE", DbType.Date, nutritionInfo.PURCHASE_DATE);
            db.AddInParameter(dbCommand, "@MONEY", DbType.Decimal, nutritionInfo.MONEY);
            db.AddInParameter(dbCommand, "@PRICE", DbType.Decimal, nutritionInfo.PRICE);
            db.AddInParameter(dbCommand, "@ADD_POINT", DbType.Int32, nutritionInfo.ADD_POINT.HasValue ? nutritionInfo.ADD_POINT.Value : 0);
            db.AddInParameter(dbCommand, "@NUMS", DbType.Int32, nutritionInfo.NUMS.HasValue ? nutritionInfo.NUMS : 0);
            db.AddInParameter(dbCommand, "@MEMO", DbType.String, nutritionInfo.MEMO);
            db.AddOutParameter(dbCommand, "@rslt", DbType.Int32, rslt);
            var ds = db.ExecuteNonQuery(dbCommand);
            rslt = int.Parse(dbCommand.Parameters["@rslt"].Value.ToString());
            return rslt == 1;
        }

        /// <summary>
        /// 获得销售编号
        /// </summary>
        /// <returns></returns>
        public static string GetSalesNutritionUniqueCode(string shopCode)
        {
            var uniqueCodePerfix = string.Format("YY{0}", shopCode);
            var db = DB.HR;

            var result = db.ExecuteDataSet(CommandType.Text,
                              string.Format(
                                  "select top 1 unique_code from sales_nutrition where unique_code like '{0}%' order by unique_code desc",
                                  uniqueCodePerfix)).Tables[0];
            if (result != null && result.Rows.Count > 0)
            {
                var num = result.Rows[0][0].ToString();
                var tmpIndex = 0;
                tmpIndex = int.Parse(num.Replace(uniqueCodePerfix, ""));
                return string.Format("{0}{1}", uniqueCodePerfix, (tmpIndex + 1).ToString().PadLeft(9, '0'));
            }
            return string.Format("{0}000000000", uniqueCodePerfix);
        }


        /// <summary>
        /// 删除营养系列数据
        /// </summary>
        /// <param name="nutritionKey"></param>
        /// <returns></returns>
        public static int RemoveSaleNutrition(string nutritionKey)
        {
            /*
            proc_sales_nutrition_delete
	        @NUTRITION_KEY uniqueidentifier,
	        @RSLT INT OUTPUT
            */
            var rslt = 0;
            var db = DB.HR;
            var dbCommand = db.GetStoredProcCommand("proc_sales_nutrition_delete");
            db.AddInParameter(dbCommand, "@NUTRITION_KEY", DbType.Guid, nutritionKey.ToGuid());
            db.AddOutParameter(dbCommand, "@RSLT", DbType.Int32, rslt);
            var ds = db.ExecuteNonQuery(dbCommand);
            return int.Parse(dbCommand.Parameters["@RSLT"].Value.ToString());
        }

        private static string BuildCondition(string field, string val, string operater)
        {
            return string.Format((operater == "=" ? " And {0}='{1}'" : " And {0} like '%{1}%'"), field, val);
        }
    }
}
